﻿using System;
using System.Collections.Generic;
using System.Data;
using CoreLab.Oracle;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;

namespace Controller
{
    /// <summary>
    /// Summary description for Event Controller
    /// </summary>
    public class NPSMarketingNameController : ControllerBase
    {
        public int InsertNPSMarketingNameData(Entities.NPSMarketingName ciNPSMarketingName)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciNPSMarketingName.sxTitle;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciNPSMarketingName.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_QUOTA";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciNPSMarketingName.iQuota;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciNPSMarketingName.iNPSMarketingType;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_I_P_NPS_MARKETING_NAME";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
            return Convert.ToInt32(lsParams[0].Value);
        }

        public void UpdateNPSMarketingNameData(Entities.NPSMarketingName ciNPSMarketingName)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_NAME_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciNPSMarketingName.iID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TITLE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciNPSMarketingName.sxTitle;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_DETAIL";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciNPSMarketingName.sxDetail;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_QUOTA";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciNPSMarketingName.iQuota;


            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciNPSMarketingName.iNPSMarketingType;


            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ciNPSMarketingName.sxUpdater;

            String sxQuery = "PR_U_P_NPS_MARKETING_NAME";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public void DeleteNPSMarketingNameData(Entities.NPSMarketingName ciNPSMarketingName)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_MARKETING_NAME_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciNPSMarketingName.iID;

            String sxQuery = "PR_D_P_NPS_MARKETING_NAME";
            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public List<Entities.NPSMarketingName> NPSMarketingNameList
        {
            get
            {
                DBLib dbl = new DBLib();

                String sxQuery =
                    @"select 
                    P_NPS_MARKETING_NAME_ID,
	                TITLE,
	                DETAIL,
                    P_NPS_MARKETING_TYPE_ID,
                    QUOTA,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE,
	                VALID_FROM,
	                VALID_TO
                from 
                    P_NPS_MARKETING_NAME
                where 
                    NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE
                ";
                DataTable dt = dbl.GetDataTable(sxQuery);

                List<Entities.NPSMarketingName> NPSMarketingNameList = new List<Entities.NPSMarketingName>();
                foreach (DataRow row in dt.Rows)
                {
                    Entities.NPSMarketingName NPSMarketingName = new Entities.NPSMarketingName();
                    NPSMarketingName.iID = Convert.ToInt32(row["P_NPS_MARKETING_NAME_ID"].ToString());
                    NPSMarketingName.sxTitle = row["TITLE"].ToString();
                    NPSMarketingName.sxDetail = row["DETAIL"].ToString();
                    NPSMarketingName.sxCreator = row["CREATE_BY"].ToString();
                    try
                    {
                        NPSMarketingName.iQuota = Convert.ToInt32(row["QUOTA"].ToString());
                        NPSMarketingName.iNPSMarketingType = Convert.ToInt32(row["P_NPS_MARKETING_TYPE_ID"].ToString());
                    }
                    catch
                    {
                        NPSMarketingName.iQuota = 10;
                        NPSMarketingName.iNPSMarketingType = 2;
                    }
                    NPSMarketingName.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                    NPSMarketingName.sxUpdater = row["UPDATE_BY"].ToString();
                    NPSMarketingName.dtmUpdated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                    NPSMarketingName.dtmValidFrom = DateTime.Parse(row["VALID_FROM"].ToString());
                    try
                    {
                        NPSMarketingTypeController mtc = new NPSMarketingTypeController();
                        NPSMarketingName.oNPSMarketingType = mtc.getNPSMarketingType(NPSMarketingName.iNPSMarketingType);

                    }
                    catch (Exception p) { errLog = p.ToString(); }
                    NPSMarketingNameList.Add(NPSMarketingName);
                }
                return NPSMarketingNameList;
            }
        }

        public List<Entities.NPSMarketingName> find(string search)
        {

            DBLib dbl = new DBLib();

            String sxQuery =
                @"select 
                    P_NPS_MARKETING_NAME_ID,
	                TITLE,
	                DETAIL,
                    QUOTA,
                    P_NPS_MARKETING_TYPE_ID,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE,
	                VALID_FROM,
	                VALID_TO
                from 
                    P_NPS_MARKETING_NAME
                where 
                    NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    upper(Title) like upper('%" + search + @"%')
                ";
            DataTable dt = dbl.GetDataTable(sxQuery);

            List<Entities.NPSMarketingName> NPSMarketingNameList = new List<Entities.NPSMarketingName>();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSMarketingName NPSMarketingName = new Entities.NPSMarketingName();
                NPSMarketingName.iID = Convert.ToInt16(row["P_NPS_MARKETING_NAME_ID"].ToString());
                NPSMarketingName.sxTitle = row["TITLE"].ToString();
                NPSMarketingName.sxDetail = row["DETAIL"].ToString();

                NPSMarketingName.sxCreator = row["CREATE_BY"].ToString();
                NPSMarketingName.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());

                try
                {
                    NPSMarketingName.iQuota = Convert.ToInt32(row["QUOTA"].ToString());
                    NPSMarketingName.iNPSMarketingType = Convert.ToInt32(row["P_NPS_MARKETING_TYPE_ID"].ToString());
                }
                catch
                {
                    NPSMarketingName.iQuota = 10;
                    NPSMarketingName.iNPSMarketingType = 2;
                }
                NPSMarketingName.sxUpdater = row["UPDATE_BY"].ToString();
                NPSMarketingName.dtmUpdated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                NPSMarketingName.dtmValidFrom = DateTime.Parse(dt.Rows[0]["VALID_FROM"].ToString());
                try
                {
                    NPSMarketingTypeController mtc = new NPSMarketingTypeController();
                    NPSMarketingName.oNPSMarketingType = mtc.getNPSMarketingType(NPSMarketingName.iNPSMarketingType);

                }
                catch (Exception p) { errLog = p.ToString(); }
                NPSMarketingNameList.Add(NPSMarketingName);
            }
            return NPSMarketingNameList;

        }

        public Entities.NPSMarketingName getNPSMarketingName(int NPSMarketingNameID)
        {
            DBLib dbl = new DBLib();
            String sxQuery =
                @"select 
                    P_NPS_MARKETING_NAME_ID,
                    P_NPS_MARKETING_TYPE_ID,
	                TITLE,
	                DETAIL,
                    QUOTA,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE,
	                VALID_FROM,
	                VALID_TO
                from 
                    P_NPS_MARKETING_NAME
                where 
                   NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    P_NPS_MARKETING_NAME_ID = :prmID
                ";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = NPSMarketingNameID;
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

            Entities.NPSMarketingName NPSMarketingName = new Entities.NPSMarketingName();
            try
            {
                NPSMarketingName.iID = Convert.ToInt16(dt.Rows[0]["P_NPS_MARKETING_NAME_ID"].ToString());
                NPSMarketingName.sxTitle = dt.Rows[0]["TITLE"].ToString();
                NPSMarketingName.sxDetail = dt.Rows[0]["DETAIL"].ToString();

                try
                {
                    NPSMarketingName.iQuota = Convert.ToInt32(dt.Rows[0]["QUOTA"].ToString());
                    NPSMarketingName.iNPSMarketingType = Convert.ToInt32(dt.Rows[0]["P_NPS_MARKETING_TYPE_ID"].ToString());
                }
                catch
                {
                    NPSMarketingName.iQuota = 10;
                    NPSMarketingName.iNPSMarketingType = 2;
                } NPSMarketingName.sxCreator = dt.Rows[0]["CREATE_BY"].ToString();
                NPSMarketingName.dtmCreated = DateTime.Parse(dt.Rows[0]["CREATE_DATE"].ToString());
                NPSMarketingName.sxUpdater = dt.Rows[0]["UPDATE_BY"].ToString();
                NPSMarketingName.dtmUpdated = DateTime.Parse(dt.Rows[0]["UPDATE_DATE"].ToString());
                NPSMarketingName.dtmValidFrom = DateTime.Parse(dt.Rows[0]["VALID_FROM"].ToString());


            }
            catch (Exception p) { errLog = p.ToString(); }
            try
            {
                NPSMarketingTypeController mtc = new NPSMarketingTypeController();
                NPSMarketingName.oNPSMarketingType = mtc.getNPSMarketingType(NPSMarketingName.iNPSMarketingType);

            }
            catch (Exception p) { errLog = p.ToString(); }
            return NPSMarketingName;
        }

        public List<Entities.NPSMarketingName> getByMarketingType(int MarketingTypeID)
        {
            DBLib dbl = new DBLib();

            String sxQuery =
                @"select 
                    P_NPS_MARKETING_NAME_ID,
	                TITLE,
	                DETAIL,
                    P_NPS_MARKETING_TYPE_ID,
                    QUOTA,
	                CREATE_BY,
	                CREATE_DATE,
	                UPDATE_BY,
	                UPDATE_DATE,
	                VALID_FROM,
	                VALID_TO
                from 
                    P_NPS_MARKETING_NAME
                where 
                    NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    P_NPS_MARKETING_TYPE_ID = :prmID
                ";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = MarketingTypeID;
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

            List<Entities.NPSMarketingName> NPSMarketingNameList = new List<Entities.NPSMarketingName>();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSMarketingName NPSMarketingName = new Entities.NPSMarketingName();
                NPSMarketingName.iID = Convert.ToInt32(row["P_NPS_MARKETING_NAME_ID"].ToString());
                NPSMarketingName.sxTitle = row["TITLE"].ToString();
                NPSMarketingName.sxDetail = row["DETAIL"].ToString();
                NPSMarketingName.sxCreator = row["CREATE_BY"].ToString();
                try
                {
                    NPSMarketingName.iQuota = Convert.ToInt32(row["QUOTA"].ToString());
                    NPSMarketingName.iNPSMarketingType = Convert.ToInt32(row["P_NPS_MARKETING_TYPE_ID"].ToString());
                }
                catch
                {
                    NPSMarketingName.iQuota = 10;
                    NPSMarketingName.iNPSMarketingType = 2;
                }
                NPSMarketingName.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                NPSMarketingName.sxUpdater = row["UPDATE_BY"].ToString();
                NPSMarketingName.dtmUpdated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                NPSMarketingName.dtmValidFrom = DateTime.Parse(row["VALID_FROM"].ToString());
                try
                {
                    NPSMarketingTypeController mtc = new NPSMarketingTypeController();
                    NPSMarketingName.oNPSMarketingType = mtc.getNPSMarketingType(NPSMarketingName.iNPSMarketingType);

                }
                catch (Exception p) { errLog = p.ToString(); }
                NPSMarketingNameList.Add(NPSMarketingName);
            }
            return NPSMarketingNameList;
        }



    }
}